﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace ATM
{
    public partial class qltaikhoan : Form
    {
        private SqlConnection conn;
        private DataTable dt = new DataTable("TB_ACCOUNT_USER");
        private DataTable dt1 = new DataTable("TB_USER");
        private DataTable dt2 = new DataTable("TB_ACCUONT");
        private SqlDataAdapter da = new SqlDataAdapter();

        private void connect()
        {

            String cn = @"Data Source=(local);Initial Catalog=ATM_PM1;Integrated Security=True";
            try
            {
                conn = new SqlConnection(cn);
                conn.Open();

            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
                MessageBox.Show("khong thanh cong");
            }
        }

        private void disconnect()
        {
            conn.Close();
            conn.Dispose();
            conn = null;
        }
        public qltaikhoan()
        {
            InitializeComponent();
        }

        private void qltaikhoan_Load(object sender, EventArgs e)
        {
            connect();
            getdata();
        }
        private void getdata()
        {
            SqlCommand command = new SqlCommand();
            command.Connection = conn;
            command.CommandType = CommandType.Text;
            command.CommandText = @"select Acc_ID[STT],User_FullName[Tên khách hàng],Acc_No[Số tài khoản],Acc_PIN[Mã Pin],Acc_Balance[Số dư tài khoản],Acc_Status[Kích hoạt] from TB_USER,TB_ACCOUNT where TB_USER.User_ID = TB_ACCOUNT.User_ID ";
            da.SelectCommand = command;
            da.Fill(dt);
            showtaikhoan.DataSource = dt;
            txttk.DataBindings.Clear();
            txttk.DataBindings.Add("Text",showtaikhoan.DataSource,"STT");
            command.CommandType = CommandType.Text;
            command.CommandText = @"Select User_ID,User_ID_Card_No,User_FullName,User_Phone,User_Address from TB_USER ";
            da.SelectCommand = command;
            da.Fill(dt1);
            showkhach.DataSource = dt1;
            try
            {
                txtid.DataBindings.Add("text", showkhach.DataSource, "User_ID");
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                MessageBox.Show("Số tài khoản này đã thay đổi!","Thông báo",MessageBoxButtons.OK);
            }
            command.CommandText = @"select * from TB_ACCOUNT";
            da.SelectCommand = command;
            da.Fill(dt2);
        }

        private void btntimkhach_Click(object sender, EventArgs e)
        {
                dt1.Clear();           
                SqlCommand command = new SqlCommand();
                command.Connection = conn;
                command.CommandType = CommandType.Text;
                command.CommandText = @"Select User_ID,User_ID_Card_No,User_FullName,User_Phone,User_Address from TB_USER where User_FullName like '%'+@ten+'%' ";
                command.Parameters.Add("@ten", SqlDbType.NVarChar, 50).Value = txttenkhach.Text;
                da.SelectCommand = command;
                da.Fill(dt1);
                if (dt1.Rows.Count > 0)
                {
                    showkhach.DataSource = dt1;
                }
                else
                {
                    MessageBox.Show("không tìm thấy tên khách hàng này !", "Thông báo", MessageBoxButtons.OK);
                }
        }

        private void btntaikhoan_Click(object sender, EventArgs e)
        {
                        
            DataRow row = dt2.NewRow();
            row["User_ID"] = txtid.Text;
            row["Acc_No"] = txttaikhoan.Text;
            row["Acc_PIN"] = txtmapin.Text;
            row["Acc_Balance"] = txttien.Text;
            dt2.Rows.Add(row);
            SqlCommand command = new SqlCommand();
            command.Connection = conn;
            command.CommandType = CommandType.Text;
            command.CommandText = @"Insert into TB_ACCOUNT(User_ID,Acc_No,Acc_PIN,Acc_Balance)
                                values(@User_ID,@Acc_No,@Acc_PIN,@Acc_Balance)";
            command.Parameters.Add("@User_ID", SqlDbType.Int, 20, "User_ID");
            command.Parameters.Add("@Acc_No", SqlDbType.NVarChar, 30, "Acc_No");
            command.Parameters.Add("@Acc_PIN", SqlDbType.NVarChar, 40, "Acc_PIN");
            command.Parameters.Add("@Acc_Balance", SqlDbType.Float, 30, "Acc_Balance");
            da.InsertCommand = command;
            da.Update(dt2);
            MessageBox.Show("Bạn đã Mở tài khoản thành công !","Thông báo",MessageBoxButtons.OK);
            this.Close();
        }

        private void btnthoat_Click(object sender, EventArgs e)
        {
            Close();
            Dispose();
        }

        private void btntimkiem_Click(object sender, EventArgs e)
        {
            dt.Clear();
            string acc = msknhaptk.Text;
            SqlCommand command = new SqlCommand();
            command.Connection = conn;
            command.CommandType = CommandType.Text;
            command.CommandText = @"Select  Acc_ID[STT],User_FullName[Tên khách hàng],Acc_No[Số tài khoản],Acc_PIN[Mã Pin],Acc_Balance[Số dư tài khoản],Acc_Status[Kích hoạt] from TB_USER,TB_ACCOUNT where Acc_No = '" + acc + "' and TB_USER.User_ID = TB_ACCOUNT.User_ID";
            da.SelectCommand = command;
            da.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                showtaikhoan.DataSource = dt;
            }
            else
            {
                MessageBox.Show("không tìm thấy tên khách hàng này !", "Thông báo", MessageBoxButtons.OK);
            }
        }

        

        private void btnthaydoi_Click(object sender, EventArgs e)
        {
            DataRow row = dt2.Select("Acc_ID=" + Convert.ToInt32(txttk.Text))[0];
            row.BeginEdit();
            row["Acc_ID"] = txttk.Text;

            if (rddichvu.Checked)
            {
                row["Acc_Status"] = 0;
            }
            else
            {
                row["Acc_Status"] = 1;
            }
            row.EndEdit();
            SqlCommand commandUpdate = new SqlCommand();
            commandUpdate.Connection = conn;
            commandUpdate.CommandType = CommandType.Text;
            commandUpdate.CommandText = @"Update TB_ACCOUNT set Acc_Status = @Acc_Status where Acc_ID=@Acc_ID";
            commandUpdate.Parameters.Add("@Acc_Status", SqlDbType.Int, 20, "Acc_Status");
            commandUpdate.Parameters.Add("@Acc_ID", SqlDbType.Int, 20, "Acc_ID");
            da.UpdateCommand = commandUpdate;
            da.Update(dt2);
            this.Close();
            
        }

        }
}
